﻿/*
Created: 14/07/2011
Modified: 14/07/2011
Project: SAICO
Model: MS SQL Server 2005
Database: MS SQL Server 2005
*/

-- Create tables section -------------------------------------------------

-- Table Consorcio

CREATE TABLE [Consorcio]
(
 [idConsorcio] Int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [cDescripcion] Varchar(max) NOT NULL,
 [idDireccion] Int NOT NULL,
 [nCantidadUF] Int NULL,
 [nEstado] Int NOT NULL,
 [idUsuarioAlta] Int NOT NULL,
 [dFechaAlta] Datetime NOT NULL,
 [idUsuarioModif] Int NULL,
 [dFechaModif] Datetime NULL,
 [idUsuarioBaja] Int NULL,
 [dFechaBaja] Datetime NULL
)
go

-- Add keys for table Consorcio

ALTER TABLE [Consorcio] ADD CONSTRAINT [Key1] PRIMARY KEY ([idConsorcio])
go

ALTER TABLE [Consorcio] ADD CONSTRAINT [idConsorcio] UNIQUE CLUSTERED ([idConsorcio])
go

ALTER TABLE [Consorcio] ADD CONSTRAINT [idDireccion] UNIQUE CLUSTERED ([idDireccion])
go

-- Table UnidadFuncional

CREATE TABLE [UnidadFuncional]
(
 [idUnidadFuncional] Int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [idConsorcio] Int NOT NULL,
 [nNroUnidadFuncional] Int NOT NULL,
 [cPropietario] Varchar(max) NULL,
 [nPiso] Int NOT NULL,
 [cDepartamento] Varchar(max) NULL,
 [nEstado] Int NULL,
 [idUsuarioAlta] Int NOT NULL,
 [dFechaAlta] Datetime NOT NULL,
 [idUsuarioModif] Int NULL,
 [dFechaModif] Datetime NULL,
 [idUsuarioBaja] Int NULL,
 [dFechaBaja] Datetime NULL
)
go

-- Add keys for table UnidadFuncional

ALTER TABLE [UnidadFuncional] ADD CONSTRAINT [Key2] PRIMARY KEY ([idConsorcio],[idUnidadFuncional])
go

ALTER TABLE [UnidadFuncional] ADD CONSTRAINT [idUnidadFuncional] UNIQUE CLUSTERED ([idUnidadFuncional])
go

-- Table TipoGasto

CREATE TABLE [TipoGasto]
(
 [idTipoGasto] Int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [cDescripcion] Varchar(max) NOT NULL,
 [nEstado] Int NOT NULL,
 [idUsuarioAlta] Int NOT NULL,
 [dFechaAlta] Datetime NOT NULL,
 [idUsuarioModif] Int NULL,
 [dFechaModif] Datetime NULL,
 [idUsuarioBaja] Int NULL,
 [dFechaBaja] Datetime NULL
)
go

-- Add keys for table TipoGasto

ALTER TABLE [TipoGasto] ADD CONSTRAINT [Key4] PRIMARY KEY ([idTipoGasto])
go

-- Table TipoExpensa

CREATE TABLE [TipoExpensa]
(
 [idTipoExpensa] Int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [cDescripcion] Varchar(max) NOT NULL,
 [nEstado] Int NOT NULL,
 [idUsuarioAlta] Int NOT NULL,
 [dFechaAlta] Datetime NOT NULL,
 [idUsuarioModif] Int NULL,
 [dFechaModif] Datetime NULL,
 [idUsuarioBaja] Int NULL,
 [dFechaBaja] Datetime NULL
)
go

-- Add keys for table TipoExpensa

ALTER TABLE [TipoExpensa] ADD CONSTRAINT [Key4] PRIMARY KEY ([idTipoExpensa])
go

-- Table Gasto

CREATE TABLE [Gasto]
(
 [idGasto] Int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [idTipoGasto] Int NOT NULL,
 [cDescripcion] Varchar(max) NOT NULL,
 [nEstado] Int NOT NULL,
 [idUsuarioAlta] Int NOT NULL,
 [dFechaAlta] Datetime NOT NULL,
 [idUsuarioModif] Int NULL,
 [dFechaModif] Datetime NULL,
 [idUsuarioBaja] Int NULL,
 [dFechaBaja] Datetime NULL
)
go

-- Add keys for table Gasto

ALTER TABLE [Gasto] ADD CONSTRAINT [Key3] PRIMARY KEY ([idGasto],[idTipoGasto])
go

ALTER TABLE [Gasto] ADD CONSTRAINT [idGasto] UNIQUE CLUSTERED ([idGasto])
go

-- Table Expensa

CREATE TABLE [Expensa]
(
 [idExpensa] Int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [idTipoExpensa] Int NOT NULL,
 [cDescripcion] Varchar(max) NOT NULL,
 [nEstado] Int NOT NULL,
 [idUsuarioAlta] Int NOT NULL,
 [dFechaAlta] Datetime NOT NULL,
 [idUsuarioModif] Int NULL,
 [dFechaModif] Datetime NULL,
 [idUsuarioBaja] Int NULL,
 [dFechaBaja] Datetime NULL
)
go

-- Add keys for table Expensa

ALTER TABLE [Expensa] ADD CONSTRAINT [Key3] PRIMARY KEY ([idExpensa],[idTipoExpensa])
go

ALTER TABLE [Expensa] ADD CONSTRAINT [idGasto] UNIQUE CLUSTERED ([idExpensa])
go

-- Table Usuario

CREATE TABLE [Usuario]
(
 [idUsuario] Int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [cUsuarioLogin] Varchar(max) NOT NULL,
 [cPassword] Varchar(max) NOT NULL,
 [cNombre] Varchar(max) NOT NULL,
 [cApellido] Varchar(max) NOT NULL,
 [nDocumento] Int NOT NULL,
 [nEstado] Int NOT NULL,
 [idUsuarioAlta] Int NOT NULL,
 [dFechaAlta] Datetime NOT NULL,
 [idUsuarioModif] Int NULL,
 [dFechaModif] Datetime NULL,
 [idUsuarioBaja] Int NULL,
 [dFechaBaja] Datetime NULL
)
go

-- Add keys for table Usuario

ALTER TABLE [Usuario] ADD CONSTRAINT [Key7] PRIMARY KEY ([idUsuario])
go

ALTER TABLE [Usuario] ADD CONSTRAINT [idUsuario] UNIQUE CLUSTERED ([idUsuario])
go

ALTER TABLE [Usuario] ADD CONSTRAINT [nDocumento] UNIQUE CLUSTERED ([nDocumento])
go

ALTER TABLE [Usuario] ADD CONSTRAINT [cUsuarioLogin] UNIQUE CLUSTERED ([cUsuarioLogin])
go

-- Table Perfil

CREATE TABLE [Perfil]
(
 [idPerfil] Int NOT NULL,
 [cDescripcion] Varchar(max) NOT NULL,
 [nEstado] Int NOT NULL,
 [idUsuarioAlta] Varchar(max) NOT NULL,
 [dFechaAlta] Datetime NOT NULL,
 [idUsuarioModif] Int NULL,
 [dFechaModif] Datetime NULL,
 [idUsuarioBaja] Int NULL,
 [dFechaBaja] Datetime NULL
)
go

-- Add keys for table Perfil

ALTER TABLE [Perfil] ADD CONSTRAINT [Key8] PRIMARY KEY ([idPerfil])
go

ALTER TABLE [Perfil] ADD CONSTRAINT [idPerfil] UNIQUE CLUSTERED ([idPerfil])
go

-- Table PerfilUsuario

CREATE TABLE [PerfilUsuario]
(
 [idPerfilUsuario] Int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [idPerfil] Int NOT NULL,
 [idUsuario] Int NOT NULL,
 [nEstado] Int NOT NULL,
 [idUsuarioAlta] Int NOT NULL,
 [dFechaAlta] Datetime NOT NULL,
 [idUsuarioModif] Int NULL,
 [dFechaModif] Datetime NULL,
 [idUsuarioBaja] Int NULL,
 [dFechaBaja] Datetime NULL
)
go

-- Add keys for table PerfilUsuario

ALTER TABLE [PerfilUsuario] ADD CONSTRAINT [Key9] PRIMARY KEY ([idPerfil],[idUsuario],[idPerfilUsuario])
go

-- Create relationships section ------------------------------------------------- 

ALTER TABLE [UnidadFuncional] ADD CONSTRAINT [FK_Consorcio_UnidadFuncional] FOREIGN KEY ([idConsorcio]) REFERENCES [Consorcio] ([idConsorcio]) ON DELETE NO ACTION
go

ALTER TABLE [PerfilUsuario] ADD CONSTRAINT [FK_Perfil_PerfilUsuario] FOREIGN KEY ([idPerfil]) REFERENCES [Perfil] ([idPerfil])
go

ALTER TABLE [PerfilUsuario] ADD CONSTRAINT [FK_Usuario_PerfilUsuario] FOREIGN KEY ([idUsuario]) REFERENCES [Usuario] ([idUsuario]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [Gasto] ADD CONSTRAINT [FK_TipoGasto_Gasto] FOREIGN KEY ([idTipoGasto]) REFERENCES [TipoGasto] ([idTipoGasto]) ON UPDATE NO ACTION ON DELETE NO ACTION
go

ALTER TABLE [Expensa] ADD CONSTRAINT [FK_TipoExpensa_Expensa] FOREIGN KEY ([idTipoExpensa]) REFERENCES [TipoExpensa] ([idTipoExpensa]) ON UPDATE NO ACTION ON DELETE NO ACTION
go




